/*
{
"状态":1,
"名称":"获取-菜单信息",
"作者":"陈泽椿",
"创建时间":"2018-06-30",
"功能":"获取-菜单信息",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/
declare @user_key int
declare @port nvarchar(200)
set @user_key='{user_key}' 
set @port='{port}'
  
select menu_key as id,case when father_menu='00000000000000000000000000000000' then '' else father_menu end  as pid,menu_code as  menu_index,menu_name as title,icon,order_index,menu_power_id,menu_id,menu_name
INTO #T
FROM y_menu 
where y_menu.menu_power_id in (
	select power_id
	from y_user,y_role_user,y_role,y_role_power,y_power
	where 1=1
	and y_user.user_key = @user_key
	and classify = '10001'
	and y_user.user_key = y_role_user.user_key
	and y_role_user.role_key = y_role.role_key
	and y_role_power.role_key = y_role.role_key
	and y_role_power.power_key = y_power.power_key
	)
and port=@port
order by order_index

;
WITH CT 
AS
(
SELECT *,CONVERT(VARCHAR(max),pid) AS cpath FROM #T WHERE pid=''
UNION ALL
SELECT A.id,A.pid,A.menu_index,A.title,A.icon,A.order_index,A.menu_power_id,A.menu_id,A.menu_name,cpath+'|'+A.pid FROM CT INNER JOIN #T A ON A.pid=CT.id
) 
SELECT id,pid,menu_index,CASE WHEN ISNULL(menu_id,'')='' then  title ELSE CT.title +'('+menu_id+')' END AS  title,ISNULL(menu_id,'') AS menu_id,icon,menu_name,order_index,menu_power_id,ISNULL(STUFF(cpath,1,1,''),'')AS cpath FROM CT 

drop table #T